package pers.cz.excel;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.util.*;

/**
 * @program: PostGirl-panent
 * @description: PoiOoxmlUtils
 * @author: Cheng Zhi
 * @create: 2022-04-15 14:57
 **/
public class PoiOoxmlUtils {

    /**
     * 解析excel文档，return Map, key is 功能用户需求，功能过程， value is 子过程描述
     * @param fileInputStream
     * @return
     * @throws IOException
     */
    public Map<String, Object> anaylizeExcel(InputStream fileInputStream, String fileName, List<String> list) throws IOException {

        Map<String, Object> firstMap = new LinkedHashMap<>();

        // 1、创建一个workbook对象
       Workbook workbook = getWorkbook(fileInputStream, fileName);
        // 2、获取workboot中表单的数量
        int numberOfSheets = workbook.getNumberOfSheets();

        for (int i=0; i<numberOfSheets; i++) {
            // 3、获取sheet页
            XSSFSheet sheet = (XSSFSheet) workbook.getSheetAt(i);
            // 存储要获取的列号
            Map<String,Integer> titleCellMap = new LinkedHashMap<String,Integer>();
            // 4、获取sheet页中的物理行数
            int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
            // 5、获取sheet页中实际使用的行数
            int lastRowNum = sheet.getLastRowNum(); // 总行数
            // 6、获取sheet页中合并单元格的个数
            int numMergedRegions = sheet.getNumMergedRegions();
            // 纵向遍历（从上往下遍历行）
            for (int j = 0; j <= lastRowNum; j++) {

                // 校验标题行
                if (!checkTitleRow(j, sheet, titleCellMap, list)) {
                    break;
                }
                if (j == 0) {
                    continue; // 跳过标题行
                }
                // 6、获取行
                XSSFRow row = sheet.getRow(j);
                if (row == null) {
                    continue; // 防止数据中间有空行
                }

                // 7、获取列
                short lastCellNum = row.getLastCellNum();
                // 横向遍历（从左到右遍历列）
                List<Integer> objects = new ArrayList();
                titleCellMap.forEach((K,V)->{
                    objects.add(V);
                });

                String cellValue1 = getCellValue(sheet, row, objects.get(0), j);
                String cellValue2 = getCellValue(sheet, row, objects.get(1), j);
                String cellValue3 = getCellValue(sheet, row, objects.get(2), j);
                Map subMap = new HashMap();
                subMap.put(cellValue2,cellValue3);

                if (firstMap.get(cellValue1) != null) {
                    List<Map<String, String>> newList = (List<Map<String, String>>) firstMap.get(cellValue1);
                    newList.add(subMap);
                    firstMap.put(cellValue1, newList);
                } else {
                    List<Map<String, String>> newList = new ArrayList<>();
                    newList.add(subMap);
                    firstMap.put(cellValue1, newList);
                }
            }
        }

        return firstMap;
    }

    /**
     * 构建第二个map
     * @param firstMap
     */
    private Map<String, Object> buildResultMap(Map<String, Object> firstMap) {

        Map<String, Object> myMap = new LinkedHashMap<>();
        for (String key : firstMap.keySet()) {

            if (key.equals("")) {
                continue;
            }
            List<Map<String, String>> maps = (List<Map<String, String>>) firstMap.get(key);
            // {真实姓名=这是我的真实名字}, {虚拟名字=这是我的虚拟名字}, {虚拟名字=这是别人的虚拟名字}, {微信名字=这是我的微信名字}
            Iterator<Map<String, String>> iterator = maps.iterator();
            Map<String, String> resultMap = new LinkedHashMap<>();
            while (iterator.hasNext()) {
                Map<String, String> map = iterator.next();
                for (String mapKey : map.keySet()) {
                    String s = resultMap.get(mapKey);
                    if (s != null ) {
                        if (s.equals("")) {
                            continue;
                        }
                        s = s + "，" + map.get(mapKey);
                        resultMap.put(mapKey,s);
                    } else {
                        if (mapKey == null || mapKey.equals("")) {
                            continue;
                        }
                        resultMap.put(mapKey, map.get(mapKey));
                    }
                }

            }

            myMap.put(key, resultMap);
        }

        return myMap;
    }

    /**
     * 获取workbook
     * @param fileInputStream
     * @param fileName
     * @return
     * @throws IOException
     */
    public static Workbook getWorkbook(InputStream fileInputStream, String fileName) throws IOException {

        // 1、创建一个workbook对象
        Workbook workbook = null;
        if (fileName.endsWith(".xlsx")) {
            workbook = new XSSFWorkbook(fileInputStream);
        } else if (fileName.endsWith(".xls")) {
            workbook = new HSSFWorkbook(fileInputStream);
        } else {
            throw new RuntimeException("请输入正确的文件格式");
        }

        return workbook;
    }


    /**
     * 获取单元值
     * @param sheet
     * @param row
     * @param column  列
     * @param rowNum  行
     * @return
     */
    private String getCellValue(Sheet sheet, XSSFRow row, Integer column, Integer rowNum) {
        String value = "";
        XSSFCell cell = row.getCell(column);
        if (cell == null || cell.equals("")) {
            return value;
        }
        // 获取单元格值，如果不是合并单元格则返回null
        String mergedRegionValue = getMergedRegion(sheet, rowNum, column);
        if (mergedRegionValue != null) {
            value = mergedRegionValue;
        } else {
            value = getCellValue(cell);
        }

        return  value;
    }
    /**
     * 标题行校验
     * @param rowNum
     * @param sheet
     * @return true：需要解析  false：不必解析
     */
    private boolean checkTitleRow(int rowNum, Sheet sheet, Map<String,Integer> map, List<String> list) {
        boolean flag = true;
        if (rowNum == 0) {

            flag = false;
            // 标题行筛选
            XSSFRow row = (XSSFRow) sheet.getRow(rowNum);
            if (row == null) {
                return false;       // 标题行为空则不解析
            }

            short lastCellNum = row.getLastCellNum();

            for (int s = 0; s<lastCellNum; s++) {
                XSSFCell cell = row.getCell(s);
                if (cell == null || cell.equals("")) {
                    continue;
                }
                String cellValue = getCellValue(cell);
                for (String keywords : list) {
                    if (cellValue != null && cellValue.contains(keywords)) {
                        map.put(cellValue,s);
                        flag = true;
                    }
                }

            }
        }
        return flag;
    }
    /**
     * 把单元格的内容转为字符串
     *
     * @param xssfCell 单元格
     * @return String
     */
    public static String getString(XSSFCell xssfCell) {
        if (xssfCell == null) {
            return "";
        }
        if (xssfCell.getCellTypeEnum() == CellType.NUMERIC) {
            return String.valueOf(xssfCell.getNumericCellValue());
        } else if (xssfCell.getCellTypeEnum() == CellType.BOOLEAN) {
            return String.valueOf(xssfCell.getBooleanCellValue());
        } else {
            return xssfCell.getStringCellValue();
        }
    }

    /**
     * 判断是否为合并单元格
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    private static boolean isMergedRegion(Sheet sheet, int row, int column) {

        int numMergedRegions = sheet.getNumMergedRegions();
        for (int i=0; i<numMergedRegions; i++) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            int firstRow = mergedRegion.getFirstRow();
            int firstColumn = mergedRegion.getFirstColumn();
            int lastColumn = mergedRegion.getLastColumn();
            int lastRow = mergedRegion.getLastRow();
            //System.out.println(firstRow + "--" + firstColumn + "--" + lastColumn + "--" +  lastRow);

            if ((row >= firstRow && row <= lastRow) && (column >= firstColumn && column <= lastColumn) ) {
                Row row1 = sheet.getRow(firstRow);
                Cell cell = row1.getCell(firstColumn);
                System.out.println(getCellValue(cell));
                return true;
            }
        }
        return false;
    }

    /**
     * 获取合并单元格的值
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static String getMergedRegion(Sheet sheet, int row, int column) {
        int numMergedRegions = sheet.getNumMergedRegions();
        for (int i=0; i<numMergedRegions; i++) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            int firstRow = mergedRegion.getFirstRow();
            int firstColumn = mergedRegion.getFirstColumn();
            int lastColumn = mergedRegion.getLastColumn();
            int lastRow = mergedRegion.getLastRow();
            //System.out.println(firstRow + "--" + firstColumn + "--" + lastColumn + "--" +  lastRow);

            if ((row >= firstRow && row <= lastRow) && (column >= firstColumn && column <= lastColumn) ) {
                Row row1 = sheet.getRow(firstRow);
                Cell cell = row1.getCell(firstColumn);
                return getCellValue(cell);
            }
        }
        return null;
    }
    /**
     * 获取单元格值
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell) {

        if (cell == null) {
            return null;
        }

/*        if (cell.getCellType() == CellType.STRING) {
            return cell.getStringCellValue();
        } else if (cell.getCellType() == CellType.NUMERIC) {
            return String.valueOf(cell.getNumericCellValue());
        } else if (cell.getCellType() == CellType.BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == CellType.FORMULA) {
            return cell.getCellFormula();
        }*/

        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            return cell.getStringCellValue();
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            return String.valueOf(cell.getNumericCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            return cell.getCellFormula();
        }
        return null;
    }
}
